﻿<%@ WebHandler Language="C#" Class="SIPO.Web.AjaxHandler.Loaddata.getinvoice" %>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using SIPO.Library.DataAccess.Transaction;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.Enum;
using SIPO.Web.Support;
using Newtonsoft.Json.Linq;
namespace SIPO.Web.AjaxHandler.Loaddata
{

    public class getinvoice : SIPO.Web.BaseHandler
    {
        recInvoiceRepo recInvoiceDal = new recInvoiceRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        string MethodName = string.Empty;
        public override void ProcessRequest(HttpContext context)
        {
            base.ProcessRequest(context);
            if (context.Request.Params["method"] != null)
                MethodName = context.Request.Params["method"];

            string queryRegistrasi = string.Empty;
            string queryInvoice = string.Empty;
            string queryAksi = string.Empty;
            
            if (MethodName == "getbybankid")
            {
                    queryRegistrasi = @"select A.DebtorSectorID
                    ,a.InvoiceNo
                    ,a.InvoiceDate
                    ,a.InvoiceValue
                    ,a.BankID
                    ,a.BeSpelled
                    ,a.PaymentNo
                    ,a.PaymentDate
                    ,a.PaymentValue
                    ,a.IsHapusBuku
                    ,a.IsHapusTagih
                    ,a.CreatedDate
                    ,a.CreatedBy
                    ,a.UpdatedDate
                    ,a.UpdatedBy
                    ,a.IsCancel
                    ,a.Balance
                    from transactions.recInvoice a 
                    inner join transactions.recbilling b on a.InvoiceNo = b.InvoiceNo
                    inner join transactions.recSectorRegistration c ON a.InvoiceNo = c.InvoiceNo 
                    inner join master.recUserSector on c.SectorRegistrationID = recUserSector.RegisterSectorID
                    LEFT JOIN master.recSector ON c.SectorID = recSector.SectorID
                    LEFT JOIN master.recSubSector ON c.SubSectorID = recSubSector.SubSectorID
                    LEFT JOIN master.recSubSubSector ON c.SubSubSectorID = recSubSubSector.SubSubSectorID
                    LEFT JOIN transactions.recInvoice ON a.InvoiceNo = recInvoice.InvoiceNo
                    where a.debtorsectorid = 0 and b.transtypeid = 1
                    AND a.paymentno is null AND a.BankID = @BankID";


                    queryInvoice = @"	SELECT 
                                        recInvoice.DebtorSectorID
                                        ,recInvoice.InvoiceNo
                                        ,recInvoice.InvoiceDate
                                        ,recInvoice.InvoiceValue
                                        ,recInvoice.BankID
                                        ,recInvoice.BeSpelled
                                        ,recInvoice.PaymentNo
                                        ,recInvoice.PaymentDate
                                        ,recInvoice.PaymentValue
                                        ,recInvoice.IsHapusBuku
                                        ,recInvoice.IsHapusTagih
                                        ,recInvoice.CreatedDate
                                        ,recInvoice.CreatedBy
                                        ,recInvoice.UpdatedDate
                                        ,recInvoice.UpdatedBy
                                        ,recInvoice.IsCancel
                                        ,recInvoice.Balance
                                        FROM transactions.recInvoice 
                                        LEFT join transactions.recSectorRegistration  on recInvoice.InvoiceNo = recSectorRegistration.InvoiceNo
                                        LEFT join master.recUserSector on recSectorRegistration.SectorRegistrationID = recUserSector.RegisterSectorID
                                        LEFT JOIN master.recDebtorSector ON recInvoice.DebtorSectorID=recDebtorSector.DebtorSectorID
                                        LEFT JOIN master.recSubSubSector ON recSubSubSector.SubSubSectorID =recDebtorSector.SubSubSectorID
                                        LEFT JOIN master.recSector ON recDebtorSector.SectorID=recSector.SectorID
                                        LEFT JOIN master.recSubSector ON recDebtorSector.SubSectorID=recSubSector.SubSectorID
                                        LEFT JOIN master.recDebtor ON recDebtorSector.DebtorID = recDebtor.DebtorID
                                        WHERE recInvoice.DebtorSectorID<>0 
                                        AND recInvoice.PaymentNo IS NULL AND recInvoice.BankID = @BankID ";           
            
                
                int bankid = Convert.ToInt32(context.Request.Params["bankid"]);

                if (base.DebtorID != null)
                {
                    queryRegistrasi = queryRegistrasi + "  AND recUserSector.UserID =" + base.UserID;
                    queryInvoice = queryInvoice + " AND recdebtor.DebtorID = " + base.DebtorID;
                }
                else
                {
                    if (base.UserTypeID == 1)
                    {
                        queryRegistrasi = queryRegistrasi + "  AND recUserSector.UserID =" + base.UserID;
                        queryInvoice = queryInvoice + " AND recdebtor.DebtorID IS NULL ";
                    }
                    else
                    {
                        queryAksi = @"
                                        select A.DebtorSectorID
                                        ,A.InvoiceNo
                                        ,A.InvoiceDate
                                        ,A.InvoiceValue
                                        ,A.BankID
                                        ,A.BeSpelled
                                        ,A.PaymentNo
                                        ,A.PaymentDate
                                        ,A.PaymentValue
                                        ,A.IsHapusBuku
                                        ,A.IsHapusTagih
                                        ,A.CreatedDate
                                        ,A.CreatedBy
                                        ,A.UpdatedDate
                                        ,A.UpdatedBy
                                        ,A.IsCancel
                                        ,A.Balance 
                                        from transactions.recInvoice a inner join transactions.recbilling b on a.InvoiceNo = b.InvoiceNo
                                        inner join transactions.recCorporateAction  on b.InvoiceNo = recCorporateAction.InvoiceNo
                                        LEFT JOIN master.recDebtorSector ON a.DebtorSectorID=recDebtorSector.DebtorSectorID
                                        LEFT JOIN master.recSubSubSector ON recSubSubSector.SubSubSectorID =recDebtorSector.SubSubSectorID
                                        LEFT JOIN master.recSector ON recDebtorSector.SectorID=recSector.SectorID
                                        LEFT JOIN master.recSubSector ON recDebtorSector.SubSectorID=recSubSector.SubSectorID
                                        LEFT JOIN master.recDebtor ON recDebtorSector.DebtorID = recDebtor.DebtorID
                                        where a.debtorsectorid = 0 and b.transtypeid = 4
                                        and a.paymentno is null AND a.BankID = @BankID ";

                    }
                }


                if (base.SubSubSectorIDs != null)
                {

                    //filter SectorID
                    queryInvoice = queryInvoice + @" AND recSector.SectorID IN ( 
                            SELECT  recSector.SectorID
                            FROM master.recSector
                            INNER JOIN master.recSubSector ON recSector.SectorID = recSubSector.SectorID
                            inner join master.recSubSubSector on recSubSubSector.SubSectorID=recSubSector.SubSectorID
                            WHERE recSubSubSector.SubSubSectorID IN(" + base.SubSubSectorIDs + @")
                            GROUP BY recSector.SectorID
                            )";


                    queryRegistrasi = queryRegistrasi + @" AND recSector.SectorID IN ( 
                            SELECT  recSector.SectorID
                            FROM master.recSector
                            INNER JOIN master.recSubSector ON recSector.SectorID = recSubSector.SectorID
                            inner join master.recSubSubSector on recSubSubSector.SubSectorID=recSubSector.SubSectorID
                            WHERE recSubSubSector.SubSubSectorID IN(" + base.SubSubSectorIDs + @")
                            GROUP BY recSector.SectorID
                            )";


                    // filter SubSectorID
                    queryInvoice = queryInvoice + @" AND recSubSector.SubSectorID IN (
                            SELECT DISTINCT recSubSector.SubSectorID
                            FROM master.recSubSector INNER JOIN master.recSubSubSector ON recSubSector.SubSectorID = recSubSubSector.SubSectorID
                            WHERE  recSubSubSector.SubSubSectorID IN (" + base.SubSubSectorIDs + @")
                            GROUP BY recSubSector.SubSectorID
                            )";


                    queryRegistrasi = queryRegistrasi + @" AND recSubSector.SubSectorID IN (
                            SELECT DISTINCT recSubSector.SubSectorID
                            FROM master.recSubSector INNER JOIN master.recSubSubSector ON recSubSector.SubSectorID = recSubSubSector.SubSectorID
                            WHERE  recSubSubSector.SubSubSectorID IN (" + base.SubSubSectorIDs + @")
                            GROUP BY recSubSector.SubSectorID
                            )";

                    //filter SubSubSectorID
                    queryInvoice = queryInvoice + @" AND recSubSubSector.SubSubSectorID IN (
                            SELECT DISTINCT recSubSubSector.SubSubSectorID
                            FROM master.recSubSubSector 
                            WHERE  recSubSubSector.SubSubSectorID IN (" + base.SubSubSectorIDs + @")
                            GROUP BY recSubSubSector.SubSubSectorID
                            ) ";

                    queryRegistrasi = queryRegistrasi + @" AND recSubSubSector.SubSubSectorID IN (
                            SELECT DISTINCT recSubSubSector.SubSubSectorID
                            FROM master.recSubSubSector 
                            WHERE  recSubSubSector.SubSubSectorID IN (" + base.SubSubSectorIDs + @")
                            GROUP BY recSubSubSector.SubSubSectorID
                            ) ";

                }

                //string subsubsectorIDsRegistrasi = string.Empty;
                //string subsubsectorIDsInvoice = string.Empty;
                //if (base.SubSubSectorIDs != null)
                //{
                //    subsubsectorIDsRegistrasi = " AND c.SubSubSectorID IN ("+base.SubSubSectorIDs+")";
                //    subsubsectorIDsInvoice = " AND recDebtorSector.SubSubSectorID in (" + base.SubSubSectorIDs + ")";
                //}
                
                
                List<recInvoice> listIncoice = recInvoiceDal.GetListByBankID(bankid,queryRegistrasi,queryInvoice,queryAksi);
                var result = listIncoice;// Where(x => x.DebtorSectorID != 0);

                context.Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(result));
 
            }

            else if (MethodName == "getbyinvoiceno")
            {
                string invoiceno = Convert.ToString(context.Request.Params["invoiceno"]);
                List<recInvoice> invoice = recInvoiceDal.GetListByInvoiceNo(invoiceno);// .GetById(invoiceno);
                var result = invoice;//.Where(x => x.InvoiceNo == invoiceno);

                context.Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(result));

            }
            else if (MethodName == "getbydebtor")
            {
                List<recInvoice> invoice = new List<recInvoice>();
                string DebtID = Convert.ToString(context.Request.Params["DebtorID"]);
                string ResID = Convert.ToString(context.Request.Params["ResID"]);
                if (context.Request.Params["edit"] != null)
                {
                    invoice = recInvoiceDal.GetListEdit(DebtID,ResID);
                }
                else
                {
                     invoice = recInvoiceDal.GetList(DebtID);
                }
               
                // .GetById(invoiceno);
                var result = invoice;//.Where(x => x.InvoiceNo == invoiceno);

                context.Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(result));

            }

            else if (MethodName == "getYearsReportYearDues")
            {
                 string billingNo = Convert.ToString(context.Request.Params["billingNo"]);
                 bool hasil =  recInvoiceDal.GetYearsReportYearsDues(billingNo);
                 System.Text.StringBuilder sbProject = new System.Text.StringBuilder();
                 sbProject.Append("{");
                 sbProject.AppendFormat("  {0}Hasil{0}:{0}{1}{0}", "\"", hasil.ToString());
                 sbProject.Append("}");

                 context.Response.Write(sbProject.ToString());

                //context.Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(sbProject.ToString()));

            }

       }

        public override bool IsReusable
        {
            get
            {
                return false;
            }
        }

    }
}